/**
 * Created by Robin on 2017/11/24.
 */
const equal = {
    $gt:'>',
    $lt:'<',
    $gte:'>=',
    $lte:'<=',
    $ne:'!=',
    $like:'like',
    $in: 'in',
    $reg: 'REGEXP'
};
class sheet {
    constructor(conn,name){
        this.conn = conn;
        this.table = name;
        this.whereString='';
        this.orWhereString='';
        this.whereArray=[];
        this.columnString= [];
        this.columnArray= [];
        this.joinArray=[];
        this.orderString = [];
        this.limitString = '';
        this.sumfield = '';
        this.insertObject = {};
        this.updateString = [];
        this.updateData = [];
        this.query=[];
        this.option=[];
        this.type='';
    }
    /**
     * 第一个参数 是需要查询的列
     * 如果有第二个参数， true| false 是 select 是否使用 DISTINCT 去重，该去重只对 select  起作用
     *
     * @memberof sheet
     */
    find(){
        var col, i;
        col = arguments[0] ? arguments[0] : null;
        if(col && col.length > 0 && typeof col != 'string'){
            for(i in col){
                this.columnString.push(this.table+'.??');
                this.columnArray.push(col[i]);
            }
        } else {
            this.columnString.push(this.table+'.*');
        }
        this.type = 'select';
        this.distinct = arguments[1] == true ? true :false;
        this.end();
        return this;
    }
    count(){
        this.type = 'count';
        this.end();
        return this;
    }
    sum(field){
        this.sumfield = field;
        this.type = 'sum';
        this.end();
        return this;
    }
    /**
     * @param {String} str 查询的条件
     * @param {String} type where 查询类型 and | or
     *
     * 该方法默认传过来的是 and
     *
     * @memberof sheet
     *
     */
    pushWhere(str,type){
        if(this.whereString === ''){
            this.whereString += str
        } else {
            this.whereString += ' ' + type + ' ' + str
        }
    }
    /**
     * @param {String} str 查询的条件
     * @param {String} type where 查询类型 and | or
     *
     * 该方法默认传过来的是 or
     * @memberof sheet
     *
     */
    orPushWhere(str,type){
        if(this.orWhereString === ''){
            this.orWhereString += str
        } else {
            this.orWhereString += ' ' + type + ' ' + str
        }
    }
    /**
     *
     * 传递参数的含义
     * 都是查询的条件有不同的写法
     * 一:对象 {id:"2222"},或 {id:{&gt;:1}}
     * 二：分开写 ("key","operation","value")
     *
     * @param {String | Object} col 条件
     * @param {String} eq 比较操作符
     * @param {String} val 对比的值
     * @returns this
     *
     * @memberof sheet
     */
    where(){
        if (!this.table && typeof this.table !== 'string') throw new Error('please select a sheet first');
        var col,eq,val, i, o;
        col = arguments[0] ? arguments[0] : null;
        eq = arguments[1] ? arguments[1] : null;
        val = arguments[2] ? arguments[2] : null;
        if(typeof col === 'string' && eq === null && val === null ){
            this.pushWhere(col,'AND');
        } else if(typeof col === 'string' && typeof eq === 'string' && typeof val === 'string' ){
            this.pushWhere(((this.joinTable === '') ? this.table : this.joinTable) + '.??' + eq + '?','AND');
            this.whereArray.push(col);
            this.whereArray.push(val);
        } else if (typeof col === 'object' && eq === null && val === null){
            for(i in col){
                if(typeof col[i] === 'string' || typeof col[i] === 'number'){
                    this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??=?','AND');
                    this.whereArray.push(i);
                    this.whereArray.push(col[i]);
                } else if (typeof col[i] === 'object'){
                    for(o in col[i]){
                        if(!equal[o]) throw new Error('error parameter ' + o);
                        switch(o){
                            case '$like': //如果like 是的只一个数组，就会默认分布循序 and like查询
                                if(col[i][o] instanceof Array){
                                    for(let v in col[i][o]){
                                        this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' ? ','AND');
                                        this.whereArray.push(i);
                                        this.whereArray.push('%' + col[i][o][v] + '%');
                                    }
                                }else{
                                    this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' ? ','AND');
                                    this.whereArray.push(i);
                                    this.whereArray.push('%' + col[i][o] + '%');
                                };

                                break;
                            case '$in':
                                this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' (?) ','AND');
                                this.whereArray.push(i);
                                this.whereArray.push(col[i][o]);
                                break;
                            case '$reg':
                                this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' (?) ','AND');
                                this.whereArray.push(i);
                                this.whereArray.push(col[i][o]);
                                break;
                            default:
                                if(typeof col[i][o] == 'object'){
                                    for(let item of col[i][o]){
                                        this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??' + equal[o] + '?','AND');
                                        this.whereArray.push(i);
                                        this.whereArray.push(item);
                                    }
                                } else {
                                    this.pushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??' + equal[o] + '?','AND');
                                    this.whereArray.push(i);
                                    this.whereArray.push(col[i][o]);
                                }
                                break;
                        }
                    }
                }
            }
        }
        return this;
    }
    orWhere(){
        if (!this.table && typeof this.table !== 'string') throw new Error('please select a sheet first');
        var col,eq,val,i,o;
        col = arguments[0] ? arguments[0] : null;
        eq = arguments[1] ? arguments[1] : null;
        val = arguments[2] ? arguments[2] : null;
        if(typeof col === 'string' && typeof eq === 'string' && typeof val === 'string' ){
            this.orPushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??' + eq + '?','OR');
            this.whereArray.push(col);
            this.whereArray.push(val);
        } else if (typeof col === 'object' && eq === null && val === null){
            for(i in col){
                if(typeof col[i] === 'string' || typeof col[i] === 'number'){
                    this.orPushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??=?','OR');
                    this.whereArray.push(i);
                    this.whereArray.push(col[i]);
                } else if (typeof col[i] === 'object'){
                    for(o in col[i]){
                        if(!equal[o]) throw new Error('error parameter ' + o);
                        switch(o){
                            case '$like':
                                this.orPushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' ? ','OR');
                                this.whereArray.push(i);
                                this.whereArray.push('%' + col[i][o] + '%');
                                break;
                            case '$in':
                                this.orPushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.?? ' + equal[o] + ' (?) ','OR');
                                this.whereArray.push(i);
                                this.whereArray.push(col[i][o]);
                                break;
                            default:
                                this.orPushWhere(((this.joinArray.length === 0) ? this.table : this.joinArray[this.joinArray.length - 1].joinTable) + '.??' + equal[o] + '?','OR');
                                this.whereArray.push(i);
                                this.whereArray.push(col[i][o]);
                                break;
                        }
                    }
                }
            }
        }
        return this;
    }
    order(){
        var col, seq, i;
        col = arguments[0] ? arguments[0] : null;
        seq = arguments[1] ? arguments[1] : null;
        if(typeof col === 'string' && typeof seq === 'number'){
            this.orderString.push(col + ' ' + ((seq === 1) ? 'ASC' : (seq === -1) ? 'DESC' : 'ASC'));
        } else if (typeof col === 'object' && seq === null){
            for(i in col){
                this.orderString.push(i + ' ' + ((col[i] == 1) ? 'ASC' : ((col[i] == -1) ? 'DESC' : 'ASC')));
            }
        }
        return this;
    }
    limit(start,num){
        this.limitString = ' LIMIT '+ start + ',' + num;
        return this;
    }
    /**
     * 参数说明
     * @param {String} table 关联查询的表名
     * @param {Object|String} col 关联查询出来的字段
     * @param {Object} opt 关联查询的配信信息
     * @param {Booble} isNoneUse 是否跳过join 语句，默认是不跳过的 false,使用join方法 ,若需要跳过，则需要传 true参数
     * @returns this
     *
     * @memberof sheet
     */
    join(){
        var table,col,opt, i, isNoneUse,joinObj = {};
        table = arguments[0] ? arguments[0] : null;
        col = arguments[1] ? arguments[1] : null;
        opt = arguments[2] ? arguments[2] : {};
        isNoneUse = arguments[3] ? arguments[3] : null;
        //判断使用 join方法，跳过
        if(isNoneUse){
            return this;
        }
        if (!table && typeof table !== 'string') throw Error('please select a sheet first');
        if(col && col.length > 0 && typeof col !== 'string'){
            for(i in col){
                this.columnString.push(table+'.?? AS '+ table + '_' + col[i]);
                this.columnArray.push(col[i]);
            }
        } else if (col && typeof col === 'string') {
            this.columnString.push(table+'.'+col);
        }
        //关联表的信息
        joinObj = {
            joinTable:table,//关联表名
            joinType:opt.type ? opt.type : 'INNER' //关联类型
        };
        if(opt.on) joinObj.joinOn = opt.on;
        this.joinArray.push(joinObj);
        return this;
    }
    insert(data){
        this.type = 'insert';
        this.insertObject = data;
        this.end();
        return this;
    }
    delete(){
        this.type = 'delete';
        this.end();
        return this;
    }
    update(data){
        var i,o;
        this.type = 'update';
        for(i in data){
            if(typeof data[i] === 'object'&& data[i]!=null){
                for(o in data[i]){
                    this.updateString.push('??=??' + o + '?');
                    this.updateData.push(i,i,data[i][o])
                }
            } else {
                this.updateString.push('??=?');
                this.updateData.push(i,data[i])
            }
        }
        this.end();
        return this;
    }
    end(){
        var joinString = '', i, query;
        if(this.joinArray.length > 0){
            for(i in this.joinArray){
                joinString += ' ' + this.joinArray[i].joinType + ' JOIN ' + this.joinArray[i].joinTable + (
                        (this.joinArray[i].joinOn && this.joinArray[i].joinOn.length > 0) ? ' ON ' + this.joinArray[i].joinOn[0] + ' = ' + this.joinArray[i].joinOn[1] : ''
                    )
            }
        }
        switch(this.type){
            case 'select':
                query = 'SELECT ' + (this.distinct == true ? ' DISTINCT ' : '')
                        // query = 'SELECT '
                    + ((this.columnString.length > 0) ? this.columnString.join(',') : '*')
                    + ' FROM ' + this.table + joinString
                    + ((this.whereString === '') ? '': ' WHERE ' + this.whereString )
                    + ((this.orWhereString === '') ? '': (((this.whereString === '') ? '(':' AND (') + this.orWhereString + ')' ))
                    + ((this.orderString.length > 0) ? ' ORDER BY ' + this.orderString.join(',') : '')
                    + ((this.limitString === '') ? '': this.limitString );
                this.query.push(query);
                this.option = this.option.concat(this.columnArray,this.whereArray);
                break;
            case 'count':
                query = 'SELECT COUNT(*) AS count'
                    + ((this.columnString.length > 0) ? ',' + this.columnString.join(',') : '')
                    + ' FROM ' + this.table + joinString
                    + ((this.whereString === '') ? '': ' WHERE ' + this.whereString )
                    + ((this.orWhereString === '') ? '': (((this.whereString === '') ? '(':' AND (') + this.orWhereString + ')' ));
                this.query.push(query);
                this.option = this.option.concat(this.columnArray,this.whereArray);
                break;
            case 'sum':
                query = 'SELECT SUM(' + this.sumfield + ') AS totalValue'
                    + ((this.columnString.length > 0) ? ',' + this.columnString.join(',') : '')
                    + ' FROM ' + this.table + joinString
                    + ((this.whereString === '') ? '': ' WHERE ' + this.whereString )
                    + ((this.orWhereString === '') ? '': (((this.whereString === '') ? '(':' AND (') + this.orWhereString + ')' ));
                this.query.push(query);
                this.option = this.option.concat(this.columnArray,this.whereArray);
                break;
            case 'insert':
                query = 'INSERT INTO ' + this.table + ' SET ?';
                this.query.push(query);
                this.option = this.option.concat([this.insertObject]);
                break;
            case 'update':
                query = 'UPDATE '
                    + this.table
                    + ' SET ' + this.updateString.join(',')
                    + ((this.whereString === '') ? '': ' WHERE ' + this.whereString )
                    + ((this.orWhereString === '') ? '': (((this.whereString === '') ? '(':' AND (') + this.orWhereString + ')' ));
                this.query.push(query);
                this.option = this.option.concat(this.updateData,this.whereArray);
                break;
            case 'delete':
                query = 'DELETE FROM ' + this.table
                    + ((this.whereString === '') ? '': ' WHERE ' + this.whereString )
                    + ((this.orWhereString === '') ? '': (((this.whereString === '') ? '(':' AND (') + this.orWhereString + ')' ));
                this.query.push(query);
                this.option = this.option.concat(this.whereArray);
                break;
        }
        this.whereString='';
        this.whereArray=[];
        this.orWhereString='';
        this.columnString= [];
        this.columnArray= [];
        this.joinArray=[];
        this.orderString = [];
        this.limitString = '';
        this.sumfield = '';
        this.insertObject = {};
        this.updateString = [];
        this.updateData = [];
        this.type='';
        this.distinct= false;
        return this;
    }
    run(){
        var queryString = this.query.join(';'), option= this.option, self = this,
            showQuery = arguments[0], showResult = arguments[1];
        this.query=[];
        this.option=[];
        if(showQuery) console.log(queryString,option);
        return new Promise(async function (resolve, reject) {
            self.conn.query(queryString,option,function(err, result, fields){
                err ? reject(err) : resolve(result);
                if(showResult) console.log(err,result);
            })
        });
    }
    one(){
        var queryString = this.query.join(';'), option= this.option, self = this,
            showQuery = arguments[0], showResult = arguments[1];
        this.query=[];
        this.option=[];
        if(showQuery) console.log(queryString,option);
        return new Promise(async function (resolve, reject) {
            self.conn.query(queryString + ' LIMIT 0,1',option,function(err, result, fields){
                //console.log('result',result);
                //console.log('err',err);
                if(result && result.length > 0){
                    resolve(result[0])
                } else {
                    resolve(null);
                }
                if(showResult) console.log(err,result);
                if(err) return reject(err);
            })
        })
    }

    //直接执行复杂的 sql语句
    executeSql(sql){
        let showQuery = arguments[1], showResult = arguments[2],self = this;
        return new Promise(async function(resolve, reject){
            self.conn.query(sql,function(err, result, fields){
                err ? reject(err) : resolve(result);
                if(showResult) console.log(err,result);
            })
        });
    };
}
module.exports = sheet;